Field of the Invention
The present invention relates to hierarchical data representation in a computer program and more particularly to processing hierarchical data processing in a relational database management system.
Description of the Related Art
Hierarchical data refers to a data structure defining data objects of a one-to-many relationship in which a parent object can have one or more children, but each child can have only one parent. Commonly envisioned as a tree, hierarchical data remains an important tool in modeling real world object relationships. Relational data, in the other hand, is best known as the basis for the relational model of database management in which all data is represented in terms of tuples, and grouped into relations so as to permit access according to first-order predicate logic. A database organized in terms of the relational model is a relational database which, in most cases, use the structured query language (SQL) data definition and query language. To that end, a table in an SQL database schema corresponds to a predicate variable; the contents of a table to a relation; key constraints, other constraints, and SQL queries correspond to predicates.
The Javascript Object Notation (JSON) is a form of hierarchical data representation. JSON is built upon a collection of name/value pairs and an ordered list of values. In JSON, a hierarchical data object includes an unordered set of name/value pairs and is denoted with a beginning left brace and an ending right brace. Each name in a JSON object is followed by a colon and the name/value pairs are separated by a comma. Of note, a JSON object can include one or more of the name/value pairs such that the name/value pairs include but a single value. Yet, a JSON object also can include a name/value pair that includes an array of values for a single name.
Of note, JSON has been viewed as a universal data representation able to be integrated in a multiplicity of programming languages. Yet, integrating JSON within a relational database is not without its challenges. Specifically, while a scalar value can be retrieved from a JSON object utilizing the “JSON VAL” function by reference to a name corresponding to the value, retrieving a value amongst a set of values in an array in a JSON is more complicated. One possible solution is to pre-specify which element of an array is desirable, but to do so would require a priori a number of elements in the array. Another possible solution is to utilize a SQL table function that can return each array element as a separate row to the relational database. Both solutions, however, cannot be indexed and therefore are not satisfactory.
The problem of processing array values in a JSON document can become compounded in attempting to process hierarchical relationships. In this instance, a function call first is required to return a set of records containing the values for the hierarchical relationship. Thereafter, an index must be built on the result set and sorted to provide ready identification of the hierarchical relationship. In doing so, however, it can be imperative to ensure type conformity so as to handle an exception during the sorting portion of the process.